package com.yangyang.autobackup;

import cn.hutool.db.Entity;
import cn.hutool.db.Session;
import cn.hutool.db.ds.simple.SimpleDataSource;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.example.FileInfo;

import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class DbUtil {

    private String dbUrl;
    private String dbPath;

    public DbUtil(String dbPath) {
        this.dbUrl = "jdbc:sqlite:" + dbPath;
        this.dbPath = dbPath;
    }

    public void createDbFile() throws Exception {
        Path dbpath = Paths.get(dbPath);
        if (!Files.exists(dbpath)) {
            new File(dbPath).getParentFile().mkdirs();
            Files.createFile(dbpath);
        }
        createTable();
    }

    // 获取数据库连接
    public Connection connect() throws SQLException {
        return DriverManager.getConnection(dbUrl);
    }

    // 创建文件信息表
    private void createTable() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS \"FILES\" (\n" +
                "\"ID\"\tTEXT,\n" +
                "\"TYPE\"\tTEXT,\n" +
                "\"FILE_NAME\"\tTEXT,\n" +
                "\"REMOTE_PATH\"\tTEXT,\n" +
                "\"LOCAL_PATH\"\tTEXT,\n" +
                "\"CREATE_DATE\"\tTEXT,\n" +
                "\"UPDATE_DATE\"\tTEXT,\n" +
                "\"SIZE\"\tTEXT,\n" +
                "\"DISPLAY_SIZE\"\tTEXT,\n" +
                "\"STATE\"\tTEXT,\n" +
                "\"BACKUP_DATE\"\tTEXT,\n" +
                "\"SORT\"\tINTEGER,\n" +
                "\"DELETE_FLAG\"\tTEXT\n" +
                ");";
        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    // 插入文件信息到数据库
    public int insertFile(List<FileInfo> files) throws SQLException {
        DataSource ds = new SimpleDataSource(dbUrl, "", "");
        Session session = Session.create(ds);

        Number number = session.queryNumber("SELECT count(*) FROM FILES;");
        long longValue = number.longValue();

        List<Entity> entityList = new LinkedList<>();
        for (FileInfo info : files) {
            longValue++;
            Entity entity = Entity.create("FILES");
            entity.put("ID", UUID.randomUUID().toString());
            entity.put("FILE_NAME", info.getFileName());
            entity.put("REMOTE_PATH", info.getRemotePath());
            entity.put("LOCAL_PATH", info.getLocalPath());
            entity.put("DISPLAY_SIZE", info.getFileSize());
            entity.put("UPDATE_DATE", info.getLastModifiedStr());
            entity.put("SORT", longValue);
            entity.put("STATE", "0");
            entity.put("DELETE_FLAG", "0");

            entityList.add(entity);
        }
        int[] insert1 = session.insert(entityList);
        session.close();
        int sum = Arrays.stream(insert1).sum();
        System.out.println("新增数据=" + sum);
        return sum;
    }


    // 查询数据库中的所有文件信息
    public List<FileInfo> getAllAddFiles() throws SQLException {
        String sql = "SELECT * FROM FILES where DELETE_FLAG = '0' and STATE = '0';";

        DataSource ds = new SimpleDataSource(dbUrl, "", "");
        Session session = Session.create(ds);
        List<Entity> query = session.query(sql);
        session.close();
        System.out.println(query.size());

        LinkedList<FileInfo> list = new LinkedList<>();
        for (Entity entity : query) {
            String id = entity.getStr("ID");
            String fileName = entity.getStr("FILE_NAME");
            String remotePath = entity.getStr("REMOTE_PATH");
            String localPath = entity.getStr("LOCAL_PATH");
            String updateDate = entity.getStr("UPDATE_DATE");
            String displaySize = entity.getStr("DISPLAY_SIZE");

            FileInfo info = new FileInfo(fileName, false, displaySize, null);
            info.setRemotePath(remotePath);
            info.setLocalPath(localPath);
            info.setLastModifiedStr(updateDate);
            info.setId(id);

            list.add(info);
        }

        return list;
    }

    // 查询数据库中的所有文件信息
    public List<FileInfo> getAllOKFiles() throws SQLException {
        String sql = "SELECT * FROM FILES where DELETE_FLAG = '0'";

        DataSource ds = new SimpleDataSource(dbUrl, "", "");
        Session session = Session.create(ds);
        List<Entity> query = session.query(sql);
        session.close();
        System.out.println(query.size());

        LinkedList<FileInfo> list = new LinkedList<>();
        for (Entity entity : query) {
            String id = entity.getStr("ID");
            String fileName = entity.getStr("FILE_NAME");
            String remotePath = entity.getStr("REMOTE_PATH");
            String localPath = entity.getStr("LOCAL_PATH");
            String updateDate = entity.getStr("UPDATE_DATE");
            String displaySize = entity.getStr("DISPLAY_SIZE");

            FileInfo info = new FileInfo(fileName, false, displaySize, null);
            info.setRemotePath(remotePath);
            info.setLocalPath(localPath);
            info.setLastModifiedStr(updateDate);
            info.setId(id);

            list.add(info);
        }

        return list;
    }

    // 查询数据库中的所有文件信息
    public Set<String> getAllOKSetNames() throws SQLException {
        List<FileInfo> allOKFiles = getAllOKFiles();
        Set<String> hashSet = new HashSet<>();
        for (FileInfo info : allOKFiles) {
            hashSet.add(info.getRemotePath());
        }
        return hashSet;
    }

    // 更新备份状态
    public int updateBakUpState(String id) throws SQLException {
        String format = DateFormatUtils.format(new Date(), "yyyy-mm-dd hh:mm:ss");
        String sql = String.format("UPDATE  FILES SET STATE='1',BACKUP_DATE='%s' WHERE ID='%s' ", format, id);
//        try (Connection conn = connect(dbUrl);
//             Statement stmt = conn.createStatement()) {
//            int update = stmt.executeUpdate(sql);
//        }


        DataSource ds = new SimpleDataSource(dbUrl, "", "");
        Session session = Session.create(ds);
        int execute = session.execute(sql);
        session.close();
        return execute;
    }


}
